package dao.daocontrollers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import workerswithdb.IDAOOrder;
import workerswithdb.SortOrdersBy;
import api.entitites.IOrder;
import api.entitites.OrderStatus;
import entitiesfactory.EntitiesFactory;

public class DAOOrder extends GenericDAO implements IDAOOrder {

	private Connection connection;
	private PreparedStatement sortAllOrders;
	private PreparedStatement sortMadingOrders;
	private PreparedStatement sortOrdersByPeriod;
	private PreparedStatement getMasterByOrder;
	private PreparedStatement getNextFreeDate;
	private PreparedStatement order_number;

	private ResultSet orderFromDb_result;
	private ResultSet sortAllOrders_result;
	private ResultSet sortMadingOrders_result;
	private ResultSet sortOrdersByPeriod_result;
	private ResultSet getMasterByOrder_result;
	private ResultSet getNextFreeDate_result;
	private ResultSet order_number_result;

	private static final String SORT_ALL_ORDERS = "select * from orders order by ?";
	private static final String SORT_MADING_ORDERS = "select * from orders where status_order is null order by ?";
	private static final String SORT_ORDERS_BY_PERIOD = " select * from orders where begining_date between ? and ? and status_order=? order by ?";
	private static final String GET_MASTER_BY_ORDER = "select master_name from orders join masters using(id_master) where number_order=? ";
	private static final String GET_NEXT_FREE_DATE = "select min(deadline_date) from orders where status_order is null";
	private static final String ORDER_NUMBER = "select max(number_order) from orders";

	private ArrayList<String> answer = new ArrayList<String>();
	private EntitiesFactory entities_factory;

	public DAOOrder() {
		super(IDAOOrder.class);
		try {
			entities_factory = EntitiesFactory.getInstance();
			connection = super.getConnection();

			sortAllOrders = connection.prepareStatement(SORT_ALL_ORDERS);
			sortMadingOrders = connection.prepareStatement(SORT_MADING_ORDERS);
			sortOrdersByPeriod = connection
					.prepareStatement(SORT_ORDERS_BY_PERIOD);
			getMasterByOrder = connection.prepareStatement(GET_MASTER_BY_ORDER);
			getNextFreeDate = connection.prepareStatement(GET_NEXT_FREE_DATE);
			order_number = connection.prepareStatement(ORDER_NUMBER);

		} catch (SQLException sqle) {

		}

	}

	public ArrayList<String> insertOrderToDB(IOrder order) {
		answer.clear();
		try {
			java.sql.Date filling_date = new java.sql.Date(order
					.getFillingDate().getTime());
			java.sql.Date begining_date = new java.sql.Date(order
					.getBeginingDate().getTime());
			java.sql.Date deadline_date = new java.sql.Date(order
					.getDeadlineDate().getTime());

			super.insert(order.getNumberOrder(), order.getOrderStatus(),
					filling_date, begining_date, deadline_date,
					order.getPrice(), order.getIdMaster(), order.getIdGarage());

			answer.add(DBMessanger.INSERT_OPERATION_SUCCESS);

		} catch (Exception e) {
			answer.clear();
			answer.add(DBMessanger.ORD_EXISTS);

		}
		return answer;
	}

	public IOrder getOrderFromDb(Integer number_order) {
		IOrder order = (IOrder) entities_factory.getObject(IOrder.class);
		try {
			orderFromDb_result = super.read(number_order);
			while (orderFromDb_result.next()) {

				int number = orderFromDb_result.getInt(2);
				String orderStatus = orderFromDb_result.getString(3);
				Date fillingDate = orderFromDb_result.getDate(4);
				Date beginingDate = orderFromDb_result.getDate(5);
				Date deadlineDate = orderFromDb_result.getDate(6);
				int price = orderFromDb_result.getInt(7);
				int master_id = orderFromDb_result.getInt(8);
				int garage_id = orderFromDb_result.getInt(9);

				order.setNumberOrder(number);
				if (orderStatus != null) {
					order.setOrderStatus(orderStatus);
				}
				order.setFillingDate(fillingDate);
				order.setBeginingDate(beginingDate);
				order.setDeadlineDate(deadlineDate);
				order.setPrice(price);
				order.sendMasterGarage(master_id, garage_id);
			}

		} catch (SQLException sqle) {
			sqle.printStackTrace();

		}
		return order;
	}

	public Integer getIdOrder(Integer order_number) {
		try {
			Integer order_id = super.getObjectId(order_number);
			return order_id;
		} catch (SQLException sqle) {
			System.out.println(DBMessanger.ORD_NOT_EXISTS);
			return null;
		}
	}

	public ArrayList<String> sortAllOrders(SortOrdersBy sort_orders_by) {
		answer.clear();
		try {
			sortAllOrders.setString(1, sort_orders_by.toString());
			sortAllOrders_result = sortAllOrders.executeQuery();
			while (sortAllOrders_result.next()) {
				String line = DBMessanger.ORDER_NUMBER
						+ sortAllOrders_result.getInt(2)
						+ DBMessanger.ORDER_STATUS
						+ sortAllOrders_result.getString(3)
						+ DBMessanger.ORDER_FILLING_DATE
						+ sortAllOrders_result.getDate(4)
						+ DBMessanger.ORDER_BEGINING_DATE
						+ sortAllOrders_result.getDate(5)
						+ DBMessanger.ORDER_DEADLINE_DATE
						+ sortAllOrders_result.getDate(6)
						+ DBMessanger.ORDER_PRICE
						+ sortAllOrders_result.getInt(7);
				answer.add(line);
			}

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ERROR);
		}
		return answer;
	}

	public ArrayList<String> sortMadingOrders(SortOrdersBy sort_orders_by) {
		answer.clear();
		try {
			sortMadingOrders.setString(1, sort_orders_by.toString());
			sortMadingOrders_result = sortMadingOrders.executeQuery();
			while (sortMadingOrders_result.next()) {
				String line = DBMessanger.ORDER_NUMBER
						+ sortMadingOrders_result.getInt(2)
						+ DBMessanger.ORDER_FILLING_DATE
						+ sortMadingOrders_result.getDate(4)
						+ DBMessanger.ORDER_BEGINING_DATE
						+ sortMadingOrders_result.getDate(5)
						+ DBMessanger.ORDER_DEADLINE_DATE
						+ sortMadingOrders_result.getDate(6)
						+ DBMessanger.ORDER_PRICE
						+ sortMadingOrders_result.getInt(7);
				answer.add(line);
			}

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ERROR);
		}
		return answer;
	}

	public ArrayList<String> sortOrdersByPeriod(Date begin, Date end,
			SortOrdersBy sort_orders_by, OrderStatus orderStatus) {
		answer.clear();
		try {

			java.sql.Date date_begin = new java.sql.Date(begin.getTime());
			java.sql.Date date_end = new java.sql.Date(begin.getTime());

			sortOrdersByPeriod.setDate(1, date_begin);
			sortOrdersByPeriod.setDate(2, date_end);
			sortOrdersByPeriod.setString(3, orderStatus.toString());
			sortOrdersByPeriod.setString(4, sort_orders_by.toString());

			sortOrdersByPeriod_result = sortOrdersByPeriod.executeQuery();

			while (sortOrdersByPeriod_result.next()) {
				String line = DBMessanger.ORDER_NUMBER
						+ sortAllOrders_result.getInt(2)
						+ DBMessanger.ORDER_STATUS
						+ sortAllOrders_result.getString(3)
						+ DBMessanger.ORDER_FILLING_DATE
						+ sortAllOrders_result.getDate(4)
						+ DBMessanger.ORDER_BEGINING_DATE
						+ sortAllOrders_result.getDate(5)
						+ DBMessanger.ORDER_DEADLINE_DATE
						+ sortAllOrders_result.getDate(6)
						+ DBMessanger.ORDER_PRICE
						+ sortAllOrders_result.getInt(7);
				answer.add(line);
			}

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ERROR);

		}
		return answer;

	}

	public ArrayList<String> getMasterByOrder(Integer number_order) {
		answer.clear();
		try {
			String master_name = "";
			getMasterByOrder.setInt(1, number_order);
			getMasterByOrder_result = getMasterByOrder.executeQuery();
			while (getMasterByOrder_result.next()) {
				master_name = getMasterByOrder_result.getString(1);
			}
			answer.add(master_name);

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ORD_NOT_EXISTS);
		}

		return answer;

	}

	public ArrayList<String> getNextFreeDate() {
		answer.clear();
		try {
			getNextFreeDate_result = getNextFreeDate.executeQuery();
			while (getNextFreeDate_result.next()) {
				answer.add(DBMessanger.NEXT_FREE_DATE
						+ getNextFreeDate_result.getDate(1));
			}

		} catch (SQLException sqle) {
			answer.clear();
			answer.add(DBMessanger.ERROR);

		}
		return answer;
	}

	public ArrayList<String> shiftDeadlineDate(Date newDate,
			Integer number_order) {
		answer.clear();
		try {
			super.update(newDate, number_order);

		} catch (Exception e) {
			answer.clear();
			answer.add(DBMessanger.ERROR);
		}
		return answer;

	}

	public ArrayList<String> markOrder(Integer order_number,
			OrderStatus order_status) {
		answer.clear();
		try {
			super.update(order_status.toString(), order_number);

		} catch (Exception e) {
			answer.clear();
			answer.add(DBMessanger.ERROR);
		}
		return answer;
	}

	public ArrayList<String> cloneOrder(Integer number_order) {
		answer.clear();
		try {
			connection.setAutoCommit(false);
			connection.setTransactionIsolation(4);
			IOrder order = getOrderFromDb(number_order).cloneOrder();
			int clone_number = 0;
			order_number_result = order_number.executeQuery();
			while (order_number_result.next()) {
				clone_number = order_number_result.getInt(1);
			}
			clone_number += 1;
			order.setNumberOrder(clone_number);
			answer.addAll(insertOrderToDB(order));
			answer.add(DBMessanger.CLONE + clone_number);
			connection.commit();

		} catch (Exception e) {
			answer.clear();
			answer.add(DBMessanger.EXCEPTION_TRANSACTION);
			e.printStackTrace();
			try {
				connection.rollback();
				connection.close();
			} catch (SQLException e1) {
				answer.clear();
				answer.add(DBMessanger.ERROR);

			}

		}

		return answer;
	}

}
